﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using CommonLibrary.Data.Sql;
using CommonLibrary.Other;
using Excel = Microsoft.Office.Interop.Excel;

namespace CommonLibrary.Data
{
    public static class ClDataTable
    {
        /// <summary>
        /// 生成字段列表
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClColumnNameList(this DataTable in_dt)
        {
            string str = "";
            foreach (DataColumn column in in_dt.Columns)
            {
                str += ", " + column.ColumnName;
            }
            str = str.Substring(2);
            //Console.WriteLine(str);
            return str;
        }

        /// <summary>
        /// 生成实体类
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClModel(this DataTable in_dt, bool dateTimeToString)
        {
            string str = @"using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace xxx
{
    public class Exxx
    {";
            foreach (DataColumn column in in_dt.Columns)
            {
                #region column
                string type = "";
                if (column.DataType.ToString() == "System.String")
                {
                    type = "string";
                }


                if (column.DataType.ToString() == "System.DateTime")
                {
                    if (dateTimeToString)
                    {
                        type = "string";
                    }
                    else
                    {
                        type = "DateTime";
                    }
                }

                if (column.DataType.ToString() == "System.Guid")
                {
                    type = "string";
                }

                if (column.DataType.ToString() == "System.Byte[]")
                {
                    type = "string";
                }


                if (column.DataType.ToString() == "System.Int16")
                {
                    type = "int";
                }
                if (column.DataType.ToString() == "System.Int32")
                {
                    type = "int";
                }
                if (column.DataType.ToString() == "System.Int64")
                {
                    type = "long";
                }

                if (column.DataType.ToString() == "System.Boolean")
                {
                    type = "bool";
                }


                if (column.DataType.ToString() == "System.Single")
                {
                    type = "double";
                }
                if (column.DataType.ToString() == "System.Double")
                {
                    type = "double";
                }

                if (column.DataType.ToString() == "System.Decimal")
                {
                    type = "double";
                }
                #endregion
                str += Environment.NewLine + "        public " + type + " " + column.ColumnName + " { get; set; } ";
            }
            str += Environment.NewLine + @"    }
}";

            str += Environment.NewLine;

            //Console.WriteLine(str);
            return str;


        }

        /// <summary>
        /// 生成ExtJs
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClJs(this DataTable in_dt)
        {
            string str = @"";

            str += @"    //声明字段ext_Fields
    var ext_Fields = 
    [" + Environment.NewLine;
            foreach (DataColumn column in in_dt.Columns)
            {
                #region column
                string type = "";
                if (column.DataType.ToString() == "System.String")
                {
                    type = "string";
                }


                if (column.DataType.ToString() == "System.DateTime")
                {
                    type = "string";
                }

                if (column.DataType.ToString() == "System.Guid")
                {
                    type = "string";
                }

                if (column.DataType.ToString() == "System.Byte[]")
                {
                    type = "string";
                }


                if (column.DataType.ToString() == "System.Int16")
                {
                    type = "int";
                }
                if (column.DataType.ToString() == "System.Int32")
                {
                    type = "int";
                }
                if (column.DataType.ToString() == "System.Int64")
                {
                    type = "int";
                }

                if (column.DataType.ToString() == "System.Boolean")
                {
                    type = "bool";
                }


                if (column.DataType.ToString() == "System.Single")
                {
                    type = "string";
                }
                if (column.DataType.ToString() == "System.Double")
                {
                    type = "string";
                }

                if (column.DataType.ToString() == "System.Decimal")
                {
                    type = "string";
                }
                #endregion

                str += "        { name: '" + column.ColumnName + "', type: '" + type + "' }";

                if (column != in_dt.Columns[in_dt.Columns.Count - 1])
                {
                    str += "," + Environment.NewLine;
                }
            }
            str += Environment.NewLine + "    ];" + Environment.NewLine + Environment.NewLine;

            #region 声明表
            str += @"    //声明表ext_Store
    var ext_Store = new Ext.data.Store({ reader: new Ext.data.JsonReader({ fields: ext_Fields }) });" + Environment.NewLine + Environment.NewLine;


            str += @"    function GetData()
    {
        showLoading(); //显示正在加载
        Ext.Ajax.request
        ({
            timeout: 3600000, //timeout 1 hour
            url: ajax_Page,
            params: { action: 'CR_GetList' },
            method: 'post',
            //async: false,//使用同步，一定要引用ext-basex.js
            success: function (response, opts)
            {
                CheckLogin(response.responseText);
                var obj = Ext.decode(response.responseText);
                ext_Store.proxy = new Ext.data.MemoryProxy(obj);

                ext_Store.load();
                hideLoading(); //隐藏正在加载
            },
            failure: function ()
            {
                hideLoading();
                Ext.Msg.alert(Identity.ErrorTitle, Identity.ErrorMsg);
            }
        });
    }
    GetData();

    ";
            #endregion


            #region 新增数据
            str +=
                 @"
   //region 新增数据

    function InsertRow()
    {
        var ext_Row = Ext.data.Record.create(ext_Fields);
        var e = new ext_Row
        ({
";
            foreach (DataColumn column in in_dt.Columns)
            {
                #region column
                string type = "";
                if (column.DataType.ToString() == "System.String")
                {
                    type = "''";
                }

                if (column.DataType.ToString() == "System.DateTime")
                {
                    type = "''";
                }

                if (column.DataType.ToString() == "System.Guid")
                {
                    type = "newGuid()";
                }

                if (column.DataType.ToString() == "System.Byte[]")
                {
                    type = "''";
                }


                if (column.DataType.ToString() == "System.Int16")
                {
                    type = "1";
                }
                if (column.DataType.ToString() == "System.Int32")
                {
                    type = "1";
                }
                if (column.DataType.ToString() == "System.Int64")
                {
                    type = "1";
                }

                if (column.DataType.ToString() == "System.Boolean")
                {
                    type = "true";
                }

                if (column.DataType.ToString() == "System.Single")
                {
                    type = "0";
                }

                if (column.DataType.ToString() == "System.Double")
                {
                    type = "0";
                }

                if (column.DataType.ToString() == "System.Decimal")
                {
                    type = "0";
                }
                #endregion

                str += "             " + column.ColumnName + ": " + type;

                if (column != in_dt.Columns[in_dt.Columns.Count - 1])
                {
                    str += "," + Environment.NewLine;
                }
            }


            str += @"
        });

        dataEditor.stopEditing();
        ext_Store.insert(0, e);
        dataGrid.getView().refresh();
        dataGrid.getSelectionModel().selectRow(0);
        dataEditor.startEditing(0);
    }

//endregion
";
            #endregion

            #region record
            str += Environment.NewLine;
            str += "//validateedit" + Environment.NewLine;
            foreach (DataColumn column in in_dt.Columns)
            {
                str += "            var " + column.ColumnName.ToLower() + " = changeRecord.get('" + column.ColumnName + "');" + Environment.NewLine;
            }

            str += Environment.NewLine;
            str += "//afteredit" + Environment.NewLine;
            foreach (DataColumn column in in_dt.Columns)
            {
                str += "            var " + column.ColumnName.ToLower() + " = record.get('" + column.ColumnName + "');" + Environment.NewLine;
            }
            #endregion





            #region TextField
            str += Environment.NewLine + Environment.NewLine;
            str += "//BuildControl" + Environment.NewLine;
            foreach (DataColumn column in in_dt.Columns)
            {
                str +=
                    @"var txt" + column.ColumnName +
                    @" = new Ext.form.TextField
({
   fieldLabel: '',
   labelStyle: 'color:red;',
   selectOnFocus: true
   width: 120,
   height: 22
});" +
                    Environment.NewLine + Environment.NewLine;
            }

            #endregion








            #region dataColumns
            str += Environment.NewLine + Environment.NewLine;
            str +=
                @"    //region dataColumns

    //dataColumns列对象   //isEdit: false,双击时只读  //isInsertDisabled: true,插时只读
    var dataColumns = 
    [
        new Ext.grid.RowNumberer({ width: 40 }),";
            foreach (DataColumn column in in_dt.Columns)
            {
                #region column
                string type = "";
                if (column.DataType.ToString() == "System.String" || column.DataType.ToString() == "System.DateTime" || column.DataType.ToString() == "System.Guid" || column.DataType.ToString() == "System.Byte[]")
                {
                    type = @"
        {
            id: '" + column.ColumnName + @"',
            header: '<span class=""must-input"">" + column.ColumnName + @"</span>',
            dataIndex: '" + column.ColumnName + @"',
            width: 80,
            sortable: true,
            menuDisabled: true,
            align: 'center',
            editor: {
                id: 'ID_" + column.ColumnName + @"',
                xtype: 'textfield',                
                //isToUpper: true,
                isToTrim: true
            }
        },";
                }





                if (column.DataType.ToString() == "System.Int16" || column.DataType.ToString() == "System.Int32" || column.DataType.ToString() == "System.Int64" || column.DataType.ToString() == "System.Single" || column.DataType.ToString() == "System.Double" || column.DataType.ToString() == "System.Decimal")
                {
                    type = @"
        {
            id: '" + column.ColumnName + @"',
            header: '<span class=""must-input"">" + column.ColumnName + @"</span>',
            dataIndex: '" + column.ColumnName + @"',
            width: 80,
            sortable: true,
            menuDisabled: true,
            align: 'right',
            editor: new Ext.form.NumberField
            ({
                id: 'ID_'" + column.ColumnName + @",
                decimalPrecision: 0,
                allowBlank: false,
                nanText: '格式输入错误'
            })
        },";
                }


                if (column.DataType.ToString() == "System.Boolean")
                {
                    type = @"
        {
            id: '" + column.ColumnName + @"',
            header: '<span class=""must-input"">" + column.ColumnName + @"</span>',
            dataIndex: '" + column.ColumnName + @"',
            xtype: 'booleancolumn',
            width: 80,
            sortable: true,
            menuDisabled: true,
            align: 'center',
            trueText: ""<input type='checkbox' disabled='disabled' checked>"",
            falseText: ""<input type='checkbox' disabled='disabled'>"",
            css: 'vertical-align: middle;',
            editor: {
                id: 'ID_'" + column.ColumnName + @",
                xtype: 'checkbox',
                align: 'center',
                listeners: {
                    check: function (obj, ischecked)
                    {
                        if (ischecked = false)
                        {
                            return;
                        }
                        var gsmr = dataGrid.getSelectionModel().getSelected();                     
                    );

                    }
                }
            }
        },";
                }


                #endregion

                str += type;


            }
            str = str.Remove(str.Length - 1);
            #endregion


            str += Environment.NewLine + "    ];" + Environment.NewLine + Environment.NewLine;

            //Console.WriteLine(str);
            return str;
        }

        /// <summary>
        /// DataSet生成Xml
        /// </summary>
        /// <param name="in_ds"></param>
        /// <returns></returns>
        public static string ClToXml(this DataSet in_ds, XmlWriteMode xmlWriteMode)
        {
            using (StringWriter writer = new StringWriter())
            {
                in_ds.WriteXml(writer, xmlWriteMode);
                return Convert.ToString(writer);
            }
        }

        /// <summary>
        /// DataSet生成Xml
        /// </summary>
        /// <param name="in_ds"></param>
        /// <returns></returns>
        public static string ClToXml(this DataSet in_ds)
        {
            return in_ds.ClToXml(XmlWriteMode.IgnoreSchema);
        }

        /// <summary>
        /// DataTable生成Xml
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClToXml(this DataTable in_dt, XmlWriteMode xmlWriteMode)
        {
            using (StringWriter writer = new StringWriter())
            {
                string tableName = in_dt.TableName;
                if (tableName.ClIsNullOrEmpty())
                {
                    in_dt.TableName = "ClToXml";
                }

                DataTable dt = new DataTable();
                dt = in_dt.Copy();
                dt.WriteXml(writer, xmlWriteMode);

                if (in_dt.TableName == "ClToXml")
                {
                    in_dt.TableName = tableName;
                }
                //Console.WriteLine(Convert.ToString(writer));
                return Convert.ToString(writer);
            }
        }

        /// <summary>
        /// DataTable生成Xml
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClToXml(this DataTable in_dt)
        {
            return in_dt.ClToXml(XmlWriteMode.IgnoreSchema);
        }

        /// <summary>
        /// DataRow生成Xml
        /// </summary>
        /// <param name="in_row"></param>
        /// <returns></returns>
        public static string ClToXml(this DataRow in_row, XmlWriteMode xmlWriteMode)
        {
            DataTable table = in_row.Table.Clone();
            table.ImportRow(in_row);

            return table.ClToXml(xmlWriteMode);
        }

        /// <summary>
        /// DataRow生成Xml
        /// </summary>
        /// <param name="in_row"></param>
        /// <returns></returns>
        public static string ClToXml(this DataRow in_row)
        {
            return in_row.ClToXml(XmlWriteMode.IgnoreSchema);
        }

        /// <summary>
        /// Xml生成DataSet
        /// </summary>
        /// <param name="in_ds"></param>
        /// <param name="xmlText"></param>
        /// <returns></returns>
        public static DataSet ClFromXml(this DataSet in_ds, string xmlText)
        {
            using (Stream stream = new MemoryStream(Encoding.UTF8.GetBytes(xmlText)))
            {
                in_ds.ReadXml(stream);
            }
            foreach (DataTable dt in in_ds.Tables)
            {
                dt.TableName = dt.TableName.Replace(".", "_");
            }
            return in_ds;
        }

        /// <summary>
        /// Xml生成DataTable
        /// </summary>
        /// <param name="in_dt"></param>
        /// <param name="xmlText"></param>
        /// <returns></returns>
        public static DataTable ClFromXml(this DataTable in_dt, string xmlText)
        {
            DataSet ds = new DataSet();
            using (Stream stream = new MemoryStream(Encoding.UTF8.GetBytes(xmlText)))
            {
                ds.ReadXml(stream);
            }
            foreach (DataTable dt in ds.Tables)
            {
                dt.TableName = dt.TableName.Replace(".", "_");
            }
            in_dt = ds.Tables[0];
            return ds.Tables[0];
        }

        /// <summary>
        /// XmlString To DataSet
        /// </summary>
        /// <param name="in_str"></param>
        /// <returns></returns>
        public static DataSet ClToDataSet(this string in_str)
        {
            DataSet dataSet = new DataSet();
            using (MemoryStream stream = new MemoryStream(Encoding.UTF8.GetBytes(in_str)))
            {
                dataSet.ReadXml(stream);
                foreach (DataTable dt in dataSet.Tables)
                {
                    dt.TableName = dt.TableName.Replace(".", "_");
                }
                return dataSet;
            }
        }

        /// <summary>
        /// XmlString To DataTable
        /// </summary>
        /// <param name="in_str"></param>
        /// <returns></returns>
        public static DataTable ClToDataTable(this string in_str)
        {
            return in_str.ClToDataSet().Tables[0];
        }

        /// <summary>
        /// 将DataSet导入Sql
        /// </summary>
        /// <param name="in_ds"></param>
        /// <returns></returns>
        public static List<string> ClToSql(this DataSet in_ds, string in_ConnectionString)
        {
            List<string> list = new List<string>();
            foreach (DataTable table in in_ds.Tables)
            {
                list.Add(ClSql.DataTableToSql(in_ConnectionString, table));
            }
            return list;
        }

        /// <summary>
        /// 将DataTable导入Sql
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClToSql(this DataTable in_dt, string in_ConnectionString)
        {
            return ClSql.DataTableToSql(in_ConnectionString, in_dt);
        }

        /// <summary>
        /// 判断DataTable是否为空
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static bool ClIsNullOrEmpty(this DataTable in_dt)
        {
            if (in_dt == null || in_dt.Rows.Count == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断DataSet是否为空
        /// </summary>
        /// <param name="in_ds"></param>
        /// <returns></returns>
        public static bool ClIsNullOrEmpty(this DataSet in_ds)
        {
            if (in_ds == null || in_ds.Tables.Count == 0)
            {
                return true;
            }

            foreach (DataTable dataTable in in_ds.Tables)
            {
                if (dataTable.ClIsNullOrEmpty() == false) return false;
            }

            return true;
        }


        #region 处理Excel

        public static DataTable DataTableReadExcel(string in_Excel, bool in_FirstRowIsHeader, string in_SheetName)
        {
            DataTable dtSheet = null;

            string m_SheetName = in_SheetName;
            string firstRowIsHeader = (in_FirstRowIsHeader ? "Yes" : "No");

            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=" + firstRowIsHeader + ";'", in_Excel);
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                var sheetNames = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索excel的架构信息

                List<string> listSheet = new List<string>();
                for (int i = 0; i < sheetNames.Rows.Count; i++)
                {
                    if (sheetNames.Rows[i]["TABLE_NAME"].ToString().Contains("FilterDatabase"))
                    {
                        continue;
                    }
                    listSheet.Add(sheetNames.Rows[i]["TABLE_NAME"].ToString());
                }

                if (listSheet.Count == 0)
                {
                    return dtSheet;
                }

                if (string.IsNullOrWhiteSpace(m_SheetName) == false)
                {
                    if (listSheet.Where(a => a.Trim().ToUpper() == in_SheetName.Trim().ToUpper()).ToList().Count == 0)
                    {
                        return dtSheet;
                    }
                }
                else
                {
                    m_SheetName = listSheet[0];
                }


                foreach (string s in listSheet)
                {
                    if (s.Trim().ToUpper() != m_SheetName.Trim().ToUpper())
                    {
                        continue;
                    }

                    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", s), cn);
                    dtSheet = new DataTable(s);
                    adapter.Fill(dtSheet);
                }

            }
            return dtSheet;
        }

        public static DataTable DataTableReadExcel(string in_Excel, bool in_FirstRowIsHeader)
        {
            return DataTableReadExcel(in_Excel, in_FirstRowIsHeader, null);
        }

        public static DataTable DataTableReadExcel(string in_Excel, bool in_FirstRowIsHeader, int in_Sheet)
        {
            DataTable dtSheet = null;

            string firstRowIsHeader = (in_FirstRowIsHeader ? "Yes" : "No");

            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=" + firstRowIsHeader + ";'", in_Excel);
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                var sheetNames = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索excel的架构信息

                List<string> listSheet = new List<string>();
                for (int i = 0; i < sheetNames.Rows.Count; i++)
                {
                    if (sheetNames.Rows[i]["TABLE_NAME"].ToString().Contains("FilterDatabase"))
                    {
                        continue;
                    }
                    listSheet.Add(sheetNames.Rows[i]["TABLE_NAME"].ToString());
                }

                if (listSheet.Count == 0)
                {
                    return dtSheet;
                }

                if (in_Sheet > listSheet.Count)
                {
                    return dtSheet;
                }


                int j = 0;
                foreach (string s in listSheet)
                {
                    if (++j != in_Sheet)
                    {
                        continue;
                    }

                    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", s), cn);
                    dtSheet = new DataTable(s);
                    adapter.Fill(dtSheet);
                }

            }
            return dtSheet;
        }

        public static DataSet DataSetReadExcel(string in_Excel, bool in_FirstRowIsHeader)
        {
            DataSet dsSheet = new DataSet((new FileInfo(in_Excel)).Name);

            string firstRowIsHeader = (in_FirstRowIsHeader ? "Yes" : "No");

            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=" + firstRowIsHeader + ";'", in_Excel);
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();
                var sheetNames = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { null, null, null, "Table" });//检索excel的架构信息

                List<string> listSheet = new List<string>();
                for (int i = 0; i < sheetNames.Rows.Count; i++)
                {
                    if (sheetNames.Rows[i]["TABLE_NAME"].ToString().Contains("FilterDatabase"))
                    {
                        continue;
                    }
                    listSheet.Add(sheetNames.Rows[i]["TABLE_NAME"].ToString());
                }

                if (listSheet.Count == 0)
                {
                    return dsSheet;
                }

                foreach (string s in listSheet)
                {

                    OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", s), cn);
                    DataTable dtSheet = new DataTable(s);
                    adapter.Fill(dtSheet);
                    dsSheet.Tables.Add(dtSheet);
                }
            }
            return dsSheet;
        }

        public static DataTable DataTableReadCsv(string in_Csv, bool in_FirstRowIsHeader)
        {
            DataTable dtSheet = null;

            string pathOnly = Path.GetDirectoryName(in_Csv);
            string fileName = Path.GetFileName(in_Csv);

            string firstRowIsHeader = (in_FirstRowIsHeader ? "Yes" : "No");

            string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Text;HDR=" + firstRowIsHeader + ";'", pathOnly);
            using (OleDbConnection cn = new OleDbConnection(connectionString))
            {
                cn.Open();

                OleDbDataAdapter adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", fileName), cn);
                dtSheet = new DataTable(fileName);
                adapter.Fill(dtSheet);
            }
            return dtSheet;
        }


        public static DataTable ClDataTableReadExcel(this DataTable in_dt, string in_Excel, bool in_FirstRowIsHeader, string in_SheetName)
        {
            in_dt = DataTableReadExcel(in_Excel, in_FirstRowIsHeader, in_SheetName);
            return in_dt;
        }

        public static DataTable ClDataTableReadExcel(this DataTable in_dt, string in_Excel, bool in_FirstRowIsHeader)
        {
            in_dt = DataTableReadExcel(in_Excel, in_FirstRowIsHeader);
            return in_dt;
        }

        public static DataTable ClDataTableReadExcel(this DataTable in_dt, string in_Excel, bool in_FirstRowIsHeader, int in_Sheet)
        {
            in_dt = DataTableReadExcel(in_Excel, in_FirstRowIsHeader, in_Sheet);
            return in_dt;
        }

        public static DataSet ClDataSetReadExcel(this DataSet in_ds, string in_Excel, bool in_FirstRowIsHeader)
        {
            in_ds = DataSetReadExcel(in_Excel, in_FirstRowIsHeader);
            return in_ds;
        }

        public static DataTable ClDataTableReadCsv(this DataTable in_dt, string in_Csv, bool in_FirstRowIsHeader)
        {
            in_dt = DataTableReadCsv(in_Csv, in_FirstRowIsHeader);
            return in_dt;
        }


        public static bool ClSaveToExcel(this DataTable in_dt, string in_fileName)
        {
            Excel.Application xlApp = new Excel.Application();
            try
            {
                object m_objOpt = System.Reflection.Missing.Value;

                Excel.Workbooks xlBooks = (Excel.Workbooks)xlApp.Workbooks;
                Excel._Workbook m_objBook = (Excel._Workbook)(xlBooks.Add(m_objOpt));

                // Add data to cells of the first worksheet in the new workbook.
                Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;


                Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.Add(m_objOpt, m_objSheets[m_objSheets.Count], 1, m_objOpt));

                if (in_dt.TableName.ClIsNullOrWhiteSpace())
                {
                    m_objSheet.Name = "Sheet-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff");
                }
                else
                {
                    m_objSheet.Name = in_dt.TableName;
                }
                m_objSheet.Cells.NumberFormatLocal = "@";


                object[,] rawData = new object[in_dt.Rows.Count + 1, in_dt.Columns.Count];
                for (int col = 0; col < in_dt.Columns.Count; col++)
                {
                    rawData[0, col] = in_dt.Columns[col].ColumnName;
                }

                // Copy the values to the object array
                for (int col = 0; col < in_dt.Columns.Count; col++)
                {
                    for (int row = 0; row < in_dt.Rows.Count; row++)
                    {
                        if (in_dt.Columns[col].DataType.Name.ToString() == "Guid")//Guid的值会变为{209BFB29-4E49-4E7A-86D7-227A300D7A86}，而不是"209BFB29-4E49-4E7A-86D7-227A300D7A86"
                        {
                            rawData[row + 1, col] = Convert.ToString(in_dt.Rows[row].ItemArray[col]);
                            continue;
                        }
                        rawData[row + 1, col] = in_dt.Rows[row].ItemArray[col];
                    }
                }


                string excelRange = "A1:" + ClType.GetExcelCol(in_dt.Columns.Count) + Convert.ToString(in_dt.Rows.Count + 1);
                m_objSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
                m_objSheet.Cells.Columns.AutoFit();


                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Activate();

                if (File.Exists(in_fileName))
                {
                    File.Delete(in_fileName);
                }
                m_objBook.SaveAs(in_fileName, 56, m_objOpt, m_objOpt, false, false);


                return true;
            }
            catch
            {
                throw;
            }
            finally
            {
                SystemHelper.KillExcel(xlApp);
            }
        }

        public static bool ClSaveToExcel(this DataSet in_ds, string in_fileName)
        {
            Excel.Application xlApp = new Excel.Application();
            try
            {
                object m_objOpt = System.Reflection.Missing.Value;

                Excel.Workbooks xlBooks = (Excel.Workbooks)xlApp.Workbooks;
                Excel._Workbook m_objBook = (Excel._Workbook)(xlBooks.Add(m_objOpt));

                // Add data to cells of the first worksheet in the new workbook.
                Excel.Sheets m_objSheets = (Excel.Sheets)m_objBook.Worksheets;


                foreach (DataTable in_dt in in_ds.Tables)
                {
                    //Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(++sheetIndex));
                    Excel._Worksheet m_objSheet = (Excel._Worksheet)(m_objSheets.Add(m_objOpt, m_objSheets[m_objSheets.Count], 1, m_objOpt));
                    if (in_dt.TableName.ClIsNullOrWhiteSpace())
                    {
                        m_objSheet.Name = "Sheet-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff");
                    }
                    else
                    {
                        m_objSheet.Name = in_dt.TableName;
                    }
                    m_objSheet.Cells.NumberFormatLocal = "@";


                    object[,] rawData = new object[in_dt.Rows.Count + 1, in_dt.Columns.Count];
                    for (int col = 0; col < in_dt.Columns.Count; col++)
                    {
                        rawData[0, col] = in_dt.Columns[col].ColumnName;
                    }

                    // Copy the values to the object array
                    for (int col = 0; col < in_dt.Columns.Count; col++)
                    {
                        for (int row = 0; row < in_dt.Rows.Count; row++)
                        {
                            if (in_dt.Columns[col].DataType.Name.ToString() == "Guid")//Guid的值会变为{209BFB29-4E49-4E7A-86D7-227A300D7A86}，而不是"209BFB29-4E49-4E7A-86D7-227A300D7A86"
                            {
                                rawData[row + 1, col] = Convert.ToString(in_dt.Rows[row].ItemArray[col]);
                                continue;
                            }
                            rawData[row + 1, col] = in_dt.Rows[row].ItemArray[col];
                        }
                    }


                    string excelRange = "A1:" + ClType.GetExcelCol(in_dt.Columns.Count) + Convert.ToString(in_dt.Rows.Count + 1);
                    m_objSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
                    m_objSheet.Cells.Columns.AutoFit();
                }

                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Delete();
                ((Excel._Worksheet)(m_objSheets.get_Item(1))).Activate();

                if (File.Exists(in_fileName))
                {
                    File.Delete(in_fileName);
                }
                m_objBook.SaveAs(in_fileName, 56, m_objOpt, m_objOpt, false, false);

                return true;
            }
            catch
            {
                throw;
            }
            finally
            {
                SystemHelper.KillExcel(xlApp);
            }
        }

        /// <summary>
        /// 打开CSV文件
        /// </summary>
        /// <returns></returns>
        public static DataTable OpenCSVFile(string filepath, bool isFirstRowHeader, Encoding encoding)
        {
            string[] Lines = File.ReadAllLines(filepath, encoding);
            string[] Fields = Lines[0].Split(new char[] { ',' });
            int Cols = Fields.GetLength(0);
            DataTable dt = new DataTable();

            int startindex = 1;
            if (isFirstRowHeader == false)
            {
                startindex = 0;
                for (int i = 0; i < Cols; i++)
                {
                    dt.Columns.Add("Column" + Convert.ToString(i + 1), typeof(string));
                }
            }
            else
            {
                for (int i = 0; i < Cols; i++)
                {
                    dt.Columns.Add(Fields[i].ToLower(), typeof(string));
                }
            }


            for (int i = startindex; i < Lines.GetLength(0); i++)
            {
                DataRow row = dt.NewRow();
                Fields = Lines[i].Split(new char[] { ',' });
                for (int j = 0; j < Cols; j++)
                {
                    row[j] = Fields[j];
                }
                dt.Rows.Add(row);
            }
            return dt;


        }

        /// <summary>
        /// 打开CSV文件
        /// </summary>
        /// <returns></returns>
        public static DataTable OpenCSVFile(string filepath, bool isFirstRowHeader)
        {
            return OpenCSVFile(filepath, isFirstRowHeader, Encoding.UTF8);
        }

        #endregion


        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
        {
            //left join
            //var query = from l in dtHeader.AsEnumerable()
            //            join r in dtDetail.AsEnumerable() on l.Field<string>("ID") equals r.Field<string>("HeaderID") into g
            //            from r in g.DefaultIfEmpty()
            //            select new
            //            {
            //                ID = l.Field<string>("ID"),
            //                ShopCode = l.Field<string>("ShopCode"),
            //                ShopName = r == null ? "" : r.Field<string>("ShopName")
            //            };
            //DataTable resultsTable = ClDataTable.CopyToDataTable(query);


            return new ObjectShredder<T>().Shred(source, null, null);
        }

        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source, DataTable table, LoadOption? options)
        {
            return new ObjectShredder<T>().Shred(source, table, options);
        }


        /// <summary>
        /// 按条件删除行
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filter"></param>
        /// <returns></returns>
        public static DataTable ClDelete(this DataTable table, string filter)
        {
            table.Select(filter).ClDelete();
            table.AcceptChanges();
            return table;
        }

        public static void ClDelete(this IEnumerable<DataRow> rows)
        {
            foreach (var row in rows)
            {
                row.Delete();
            }
        }

        /// <summary>
        /// 转为层级关系
        /// </summary>
        /// <param name="in_arr"></param>
        /// <param name="in_split"></param>
        /// <returns></returns>
        public static DataTable MakeParentChild(string[] in_arr, string in_split)
        {
            DataTable dtAll = new DataTable("MakeParentChild");
            dtAll.Columns.Add("id", typeof(string));
            dtAll.Columns.Add("name", typeof(string));
            dtAll.Columns.Add("parentid", typeof(string));

            HashSet<string> hs = new HashSet<string>();

            foreach (string txFile in in_arr)
            {
                DataTable dtTmp = MakeParentChild(txFile, in_split);

                foreach (DataRow row in dtTmp.Rows)
                {
                    if (hs.Add(row["id"].ToString()) == false)
                    {
                        continue;
                    }

                    dtAll.ImportRow(row);
                }

            }
            return dtAll;
        }

        /// <summary>
        /// 转为层级关系
        /// </summary>
        /// <param name="in_str"></param>
        /// <param name="in_split"></param>
        /// <returns></returns>
        public static DataTable MakeParentChild(string in_str, string in_split)
        {
            //输入字符串"2012\2012_11\2012_11_02\FEE2012_11_02.TXT"
            //转为   id               name       parentid
            //       2012             2012       ""   
            //       2012\2012_11     2012_11    2012

            DataTable dt = new DataTable("MakeParentChild");
            dt.Columns.Add("id", typeof(string));
            dt.Columns.Add("name", typeof(string));
            dt.Columns.Add("parentid", typeof(string));

            string[] arr = System.Text.RegularExpressions.Regex.Split(in_str, in_split.Replace(@"\", @"\\"), System.Text.RegularExpressions.RegexOptions.IgnoreCase);

            for (int i = 0; i < arr.Length; i++)
            {
                string id = "";
                string parentid = "";
                for (int j = 0; j <= i; j++)
                {
                    id += in_split + arr[j];
                    if (j < i)
                    {
                        parentid += in_split + arr[j];
                    }
                }

                id = id.Substring(in_split.Length);
                parentid = parentid.Length == 0 ? "" : parentid.Substring(in_split.Length);

                DataRow row = dt.NewRow();
                row["id"] = id;
                row["name"] = arr[i];
                row["parentid"] = parentid;
                dt.Rows.Add(row);
            }

            return dt;
        }

        public static List<T> ClToList<T>(this DataTable in_dt) where T : new()
        {
            return DataExtensions.DataTableToList<T>(in_dt);
        }

        /// <summary>
        /// DataTable的行转为字典
        /// </summary>
        /// <param name="in_DataRow"></param>
        /// <returns></returns>
        public static Dictionary<string, object> DataRowToDictionary(DataRow in_DataRow)
        {
            Dictionary<string, object> dictionary = new Dictionary<string, object>();
            foreach (DataColumn column in in_DataRow.Table.Columns)
            {
                dictionary.Add(column.ColumnName, in_DataRow[column.ColumnName]);
            }
            return dictionary;
        }

        /// <summary>
        /// DataTable To Model
        /// </summary>
        /// <param name="in_dt"></param>
        /// <returns></returns>
        public static string ClToModel(this DataTable in_dt)
        {
            string result = @"    public class ClassModel 
    {" + Environment.NewLine;

            foreach (DataColumn dataColumn in in_dt.Columns)
            {
                string txType = "";
                switch (dataColumn.DataType.ToString())
                {
                    #region 类型转换

                    case "System.String":
                        {
                            txType = "string";
                            break;
                        }
                    case "System.Byte":
                        {
                            txType = "string";
                            break;
                        }

                    case "System.Guid":
                        {
                            txType = "string";
                            break;
                        }


                    case "System.Decimal":
                        {
                            txType = "double";
                            break;
                        }
                    case "System.Int16":
                        {
                            txType = "int";
                            break;
                        }
                    case "System.Int32":
                        {
                            txType = "int";
                            break;
                        }
                    case "System.Int64":
                        {
                            txType = "long";
                            break;
                        }

                    case "System.Boolean":
                        {
                            txType = "bool";
                            break;
                        }

                    case "System.DateTime":
                        {
                            txType = "DateTime";
                            break;
                        }

                    default:
                        {
                            txType = "__UNKNOWN__" + dataColumn.DataType.ToString();
                            break;
                        }
                    #endregion
                }
                if ((dataColumn.AllowDBNull) && (txType.ClIn("string", "byte[]") == false))
                {
                    txType = txType + "?";
                }

                result += @"        public " + txType + " " + dataColumn.ColumnName + " { get; set; }" + Environment.NewLine;
            }

            result += @"    }
";
            return result;
        }

        /// <summary>
        /// DataTable To Model
        /// </summary>
        /// <param name="in_dt"></param>
        /// <param name="in_file"></param>
        public static void ClToModel(this DataTable in_dt, string in_file)
        {
            string result = in_dt.ClToModel();
            in_file.ClWriteFile(result);
        }


    }


}
